﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace GestorDeFlotasDesktop.Listado
{
    public partial class Listado : Form
    {
        public Listado()
        {
            InitializeComponent();
            tb_mes_fin.Enabled = false;
            //'2009-01-08 12:00:00.000'
            cb_tipo_listado.Items.Add("choferes");
            cb_tipo_listado.Items.Add("taxis");
            cb_tipo_listado.Items.Add("clientes");
        }

        private void Listado_Load(object sender, EventArgs e)
        {

        }

        private void cb_tipo_listado_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void aceptar_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = Modelo.Modelo.createConnection())
            {
                if (tb_anio_ini.Text != "" && tb_anio_fin.Text != "" && tb_mes_ini.Text != "")
                {
                    if (Int32.Parse(tb_mes_ini.Text) == 1)
                    {
                        tb_mes_fin.Text = "04";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 2)
                    {
                        tb_mes_fin.Text = "05";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 3)
                    {
                        tb_mes_fin.Text = "06";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 4)
                    {
                        tb_mes_fin.Text = "07";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 5)
                    {
                        tb_mes_fin.Text = "08";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 6)
                    {
                        tb_mes_fin.Text = "09";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 7)
                    {
                        tb_mes_fin.Text = "10";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 8)
                    {
                        tb_mes_fin.Text = "11";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 9)
                    {
                        tb_mes_fin.Text = "12";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 10)
                    {
                        tb_mes_fin.Text = "01";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 11)
                    {
                        tb_mes_fin.Text = "02";
                    }
                    if (Int32.Parse(tb_mes_ini.Text) == 12)
                    {
                        tb_mes_fin.Text = "03";
                    }
 

                    string fecha_ini = "'" + tb_anio_ini.Text + "-01-" + tb_mes_ini.Text + "'";
                    string fecha_fin = "'" + tb_anio_fin.Text + "-01-" + tb_mes_fin.Text + "'";



                    //===============================================================================================
                    //0: choferes
                    //===============================================================================================
                    if (cb_tipo_listado.SelectedIndex.ToString() == "0")
                    {
                        SqlDataAdapter da = new SqlDataAdapter("SELECT     TOP (5) Id_Chofer,(SELECT     Ch_Dni FROM          NUNCA_TAXI.Choferes WHERE      (NUNCA_TAXI.Viajes.Id_Chofer = Id_Chofer)) AS DNI,(SELECT     Ch_Nombre FROM          NUNCA_TAXI.Choferes AS Choferes_2 WHERE      (NUNCA_TAXI.Viajes.Id_Chofer = Id_Chofer)) AS NOMBRE,(SELECT     Ch_Apellido FROM          NUNCA_TAXI.Choferes AS Choferes_1 WHERE      (NUNCA_TAXI.Viajes.Id_Chofer = Id_Chofer)) AS APELLIDO, SUM(Vi_Importe) AS GastoTotal FROM         NUNCA_TAXI.Viajes WHERE     (Vi_Fecha BETWEEN " + fecha_ini + " AND " + fecha_fin + ") GROUP BY Id_Chofer ORDER BY gastototal DESC", con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }




























                    //===============================================================================================
                    //1: taxis
                    //===============================================================================================
                    if (cb_tipo_listado.SelectedIndex.ToString() == "1")
                    {
                        SqlDataAdapter da = new SqlDataAdapter("SELECT     TOP (5) Id_Taxi,(SELECT     Ta_Patente FROM          NUNCA_TAXI.Taxis WHERE      (NUNCA_TAXI.Viajes.Id_Taxi = Id_Taxi)) AS patente,(SELECT     Ta_Licencia FROM          NUNCA_TAXI.Taxis AS Choferes_2 WHERE      (NUNCA_TAXI.Viajes.Id_Taxi = Id_Taxi)) AS licencia,(SELECT     Ma_NombreTaxi FROM          NUNCA_TAXI.MarcasTaxi WHERE      (Id_MarcaTaxi =(SELECT     Id_MarcaTaxi FROM          NUNCA_TAXI.Taxis AS Taxis_1 WHERE      (NUNCA_TAXI.Viajes.Id_Taxi = Id_Taxi)))) AS MARCA, SUM(Vi_Importe) AS GastoTotal FROM         NUNCA_TAXI.Viajes WHERE     (Vi_Fecha BETWEEN " + fecha_ini + " AND " + fecha_fin + ")GROUP BY Id_Taxi ORDER BY gastototal DESC", con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }
































                    //===============================================================================================
                    //2: clientes
                    //===============================================================================================
                    if (cb_tipo_listado.SelectedIndex.ToString() == "2")
                    {
                        SqlDataAdapter da = new SqlDataAdapter("SELECT TOP (5) Id_Cliente,(SELECT Cl_Dni FROM NUNCA_TAXI.Clientes WHERE (NUNCA_TAXI.Viajes.Id_Cliente = Id_Cliente)) AS DNI,(SELECT Cl_Nombre FROM NUNCA_TAXI.Clientes AS Clientes_2 WHERE (NUNCA_TAXI.Viajes.Id_Cliente = Id_Cliente)) AS NOMBRE,(SELECT Cl_Apellido FROM NUNCA_TAXI.Clientes AS Clientes_1 WHERE (NUNCA_TAXI.Viajes.Id_Cliente = Id_Cliente)) AS APELLIDO, SUM(Vi_Importe) AS GastoTotal FROM NUNCA_TAXI.Viajes WHERE (Id_Cliente IS NOT NULL) AND (Vi_Fecha BETWEEN " + fecha_ini + " AND " + fecha_fin + ") GROUP BY Id_Cliente ORDER BY gastototal DESC", con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }
                }
            }
        }
    }
}
